// License Notification
//======================================================
// Copyright (c) 2010 Channel Intelligence Inc.
// All rights reserved. This program and the accompanying materials
// are made available under the terms of the Eclipse Public License v1.0
// which accompanies this distribution, and is available at
// http://www.eclipse.org/legal/epl-v10.html
//
// ======================================================




using System;
using System.Collections.Generic;
using System.Xml.Linq;
using System.Diagnostics;
using System.IO;
using System.IO.Compression;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Reflection;
using System.Threading;
using CIP4.Item;
using CIP4.Common;
using CIP4.Simple;
using CIP4.RulesEngine;
using MySql.Data.MySqlClient;


namespace CIP4.DataProvider.MySql
{
    public class DataProvider : CIP4.Simple.DataProvider, IDataProvider
    {

        private int MAXRETRIES = 6;

        private int RETRYBACKOFFDELTA = 4; // Results in waits of 0.004, 0.016, 0.064, 0.256, 1.024, and 4.096 seconds

        private string InnerConnectionString { get; set; }

        #region IDataProvider Members

        public string ConnectionString { get; private set; }

        IDataProvider Cache { get; set; }

        private string DatabaseName { get; set; }

        public DataProvider (CIP4ConnectionAttribute DataProviderConnection, IDataProvider cachingProvider, LoggerDelegate logger)
            : base (DataProviderConnection)
        {
            if (!(DataProviderConnection is CIP4MySqlConnectionAttribute))
                LoggerWrapper(MajorEnum.Error, MinorEnum.UnexpectedConnectionType,
                    "{0} is not an acceptable connection type", new object[] { DataProviderConnection.ToString() }, logger);
            else
            {
                LoggerWrapper(MajorEnum.Trace, MinorEnum.InitializingProvider,
                    "Initalizing Provider: {0}", new object[] { DataProviderConnection }, logger);


                ConnectionString = DataProviderConnection.ToString();

                CIP4MySqlConnectionAttribute connection = (CIP4MySqlConnectionAttribute)DataProviderConnection;
                DatabaseName = connection.Database;
                InnerConnectionString = string.Format("Database={0};Data Source={1};User Id={2};Password={3}", connection.Database, connection.Host, connection.UserId, connection.Password);
                ConnectionString = string.Format("Database={0};Data Source={1};User Id={2}", connection.Database, connection.Host, connection.UserId);

                Cache = cachingProvider;
            }
        }

        public void DeleteCollection(CollectionType collection, LoggerDelegate logger)
        {
            if (!LoggerWrapper(MajorEnum.Trace, MinorEnum.ActionStart,
                "Started {0}", new object[] { MinorEnum.DeleteCollection }, logger))
                return;

            PurgeCollectionCmd(collection, logger);

            LoggerWrapper(MajorEnum.Information, MinorEnum.DeleteCollection,
                "Collection deleted {0}", new object[] { collection }, logger);
        }

        public override IEnumerable<object> GetItems(CollectionType collection, IBooleanValue selectionExpression, CursorType cursor)
        {
            List<object> items = new List<object>();
            if (!LoggerWrapper(MajorEnum.Trace, MinorEnum.ActionStart,
                "Started {0}", new object[] { MinorEnum.GetItems }, cursor.Logger))
                return items;

            try
            {
                ISerializerType serializer = cursor.GetSerializer(SerializerKindEnum.CIP4Xml);

                string lookForExternalId = GetExternalIdValueFromSelectionExpression(selectionExpression, serializer);

                if (Cache != null && !string.IsNullOrEmpty(lookForExternalId))
                {
                    foreach (object item in Cache.GetItems(collection, selectionExpression, cursor))
                    {
                        items.Add(item);
                        return items;
                    }
                }

                if (!string.IsNullOrEmpty(lookForExternalId))
                    items = GetItemsFromTheTableViaExternalID(collection, lookForExternalId, selectionExpression, cursor);
                else
                    items = GetItemsFromTheTable(collection, selectionExpression, cursor);
                return items;
            }
            finally
            {
                LoggerWrapper(MajorEnum.Information, MinorEnum.GetItems,
                    "Get {0} items from {1}", new object[] { items.Count, collection }, cursor.Logger);
            }
        }

        public override void PurgeItems(IEnumerable<ExternalIDType> externalIDs, CollectionType collection, GetSerializerDelegate getSerializer)
        {
            ISerializerType serializer = getSerializer(SerializerKindEnum.CIP4Xml);

            if (!LoggerWrapper(MajorEnum.Trace, MinorEnum.ActionStart,
                "Started {0}", new object[] { MinorEnum.PurgeItems }, serializer.Logger))
                return;

            int itemCount = 0;
            
            try
            {
                if (Cache != null)
                    Cache.PurgeItems(externalIDs, collection, getSerializer);

                foreach (ExternalIDType externalID in externalIDs)
                {
                    ExecutePurgeCmd(externalID.ToString(), collection, serializer);

                    RemoveItemsStreamResolver(serializer, collection, externalID.ToString());

                    itemCount++;
                }
            }
            catch (Exception ex)
            {
                if (!LoggerWrapper(MajorEnum.Error, MinorEnum.ErrorPurgingItems, "PurgeItems Error: {0}", new object[] { ex }, serializer.Logger))
                    throw;
            }
            LoggerWrapper(MajorEnum.Information, MinorEnum.PurgeItems,
                "{0} items deleted from {1}", new object[] { itemCount, collection.Name }, serializer.Logger);
        }

        public void SaveItems(IEnumerable<object> items, CollectionType collection, GetSerializerDelegate getSerializer)
        {
            ISerializerType serializer = getSerializer(SerializerKindEnum.CIP4Xml);

            if (!LoggerWrapper(MajorEnum.Trace, MinorEnum.ActionStart,
                "Started {0}", new object[] { MinorEnum.SaveItems }, serializer.Logger))
                return;

            int itemCount = 0;

            try
            {
                foreach (object objectItem in items)
                {
                    SaveItem(objectItem, collection, serializer);
                    itemCount++;
                }
            }
            catch (Exception ex)
            {
                LoggerWrapper(MajorEnum.Error, MinorEnum.ErrorSavingItems, "SaveItems Error: {0}", new object[] { ex }, serializer.Logger);
            }
            LoggerWrapper(MajorEnum.Information, MinorEnum.SaveItems,
                "{0} items saved in {1}", new object[] { itemCount, collection.Name }, serializer.Logger);
        }

        public void Terminate(LoggerDelegate logger)
        {
        }

        #endregion

        #region Helper Functions

        private void PurgeCollectionCmd(CollectionType collection, LoggerDelegate logger)
        {
            MySqlCommand cmd;
            MySqlConnection connection = null;

            using (connection = new MySqlConnection(InnerConnectionString))
            {
                connection.Open();
                {
                    //  create command (assumes connection already open)
                    cmd = connection.CreateCommand();
                    cmd.CommandTimeout = 180;

                    string text = string.Format("DELETE FROM item WHERE CollectionID = '{0}'",
                        collection.ExternalIDGuid().ToString());

                    cmd.CommandType = System.Data.CommandType.Text;
                    cmd.CommandText = text;

                    int count = cmd.ExecuteNonQuery();
                }
                {
                    //  create command (assumes connection already open)
                    cmd = connection.CreateCommand();
                    cmd.CommandTimeout = 180;

                    string text = string.Format("DELETE FROM ItemIndex WHERE CollectionID = '{0}'",
                        collection.ExternalIDGuid().ToString());

                    cmd.CommandType = System.Data.CommandType.Text;
                    cmd.CommandText = text;

                    int count = cmd.ExecuteNonQuery();
                }
            }
        }

        private bool ExecutePurgeCmd(string externalID, CollectionType collection, ISerializerType serializer)
        {
            MySqlCommand cmd;
            MySqlConnection connection = null;

            using (connection = new MySqlConnection(InnerConnectionString))
            {
                connection.Open();
                {   // delete the record from the item table
                    cmd = connection.CreateCommand();
                    cmd.CommandTimeout = 180;

                    string text = string.Format("DELETE FROM item WHERE CollectionID = '{0}' AND ExternalID = '{1}'",
                        collection.ExternalIDGuid().ToString(), externalID);

                    cmd.CommandType = System.Data.CommandType.Text;
                    cmd.CommandText = text;

                    int count = cmd.ExecuteNonQuery();
                }
                // if there are any non-externalid keys then clear out the itemIndex entries
                if (serializer.GetKeys().Count(k=>k.Value > 0) > 0)
                {   // delete records from the index table (if any)
                    cmd = connection.CreateCommand();
                    cmd.CommandTimeout = 180;

                    string text = string.Format("DELETE FROM ItemIndex where CollectionID = '{0}' AND ItemExternalID = '{1}'",
                        collection.ExternalIDGuid().ToString(), externalID);

                    cmd.CommandType = System.Data.CommandType.Text;
                    cmd.CommandText = text;

                    int count = cmd.ExecuteNonQuery();
                }
                LoggerWrapper(MajorEnum.Trace, MinorEnum.ItemDeleted, "Item {0} was deleted", new object[] { externalID }, serializer.Logger);
            }
            return true;
        }

        private void SaveItem(object item, CollectionType collection, ISerializerType serializer)
        {
            LoggerDelegate logger = serializer.Logger;

            int retryCount = 0;

            if (!SetHeaderProperties(item, collection, serializer))
                return;

            do
            {
                using (MySqlConnection connection = new MySqlConnection(InnerConnectionString))
                {
                    connection.Open();
                    using (MySqlTransaction transaction = connection.BeginTransaction())
                    {
                        try
                        {
                            if (!ExecuteSaveCmd(item, collection, connection, transaction, serializer))
                                break;

                            PrepareSaveIndexEntriesCmd(item, collection, connection, transaction, serializer);

                            SaveItemsStreamResolver(serializer, collection, connection, transaction, serializer.GetExternalID(item).ToString());

                            transaction.Commit();
                            break;
                        }
                        catch (ArgumentException ex)
                        {
                            if (transaction != null && transaction.Connection != null && transaction.Connection.State == System.Data.ConnectionState.Open)
                                transaction.Rollback();
                            if (ex.Message.StartsWith("The DataSource specified in the conection is not reachable"))
                            {
                                retryCount += 1;
                                if (!LoggerWrapper(MajorEnum.Warning, MinorEnum.RetryAccessingDataStore,
                                    "Retrying after {0}", new object[] { ex.Message }, logger))
                                    throw;
                                Thread.Sleep((int)Math.Pow(RETRYBACKOFFDELTA, retryCount));
                            }
                            else
                                throw;
                        }
                        catch (InvalidOperationException ex)
                        {
                            if (transaction != null && transaction.Connection != null && transaction.Connection.State == System.Data.ConnectionState.Open)
                                transaction.Rollback();
                            if (ex.Message.StartsWith("This SqlTransaction has completed; it is no longer usable"))
                            {
                                retryCount += 1;
                                if (!LoggerWrapper(MajorEnum.Warning, MinorEnum.RetryAccessingDataStore,
                                    "Retrying after {0}", new object[] { ex.Message }, logger))
                                    throw;
                                Thread.Sleep((int)Math.Pow(RETRYBACKOFFDELTA, retryCount));
                            }
                            else
                                throw;
                        }
                        catch (Exception ex)
                        {
                            if (transaction != null && transaction.Connection != null && transaction.Connection.State == System.Data.ConnectionState.Open)
                                transaction.Rollback();

                            LoggerWrapper(MajorEnum.Error, MinorEnum.ErrorSavingItems,
                                "Error encountered saving {0}: {1}", new object[] { item.ToString(), ex }, serializer.Logger);

                            break;
                        }
                    }

                    connection.Close();
                }
            } while (retryCount < MAXRETRIES);
            if (retryCount == MAXRETRIES)
                LoggerWrapper(MajorEnum.Error, MinorEnum.RetryCountExceeded, "Retry count exceeded accessing collection {0}", new object[] { collection.Name }, logger);
        }

        private bool PrepareSaveIndexEntriesCmd(object item, CollectionType collection, MySqlConnection connection, MySqlTransaction transaction, ISerializerType serializer)
        {
            try
            {
                if (serializer.GetKeys().Count(k => k.Value > 0) == 0) // just the externalID - can get out now
                    return true;
                LoggerDelegate logger = serializer.Logger;

                //  create command (assumes connection already open)
                MySqlCommand cmd = connection.CreateCommand();
                cmd.Transaction = transaction;
                cmd.CommandTimeout = 180;

                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.CommandText = string.Format("{0}.uspSaveItemIndexes0404", DatabaseName.ToLower());
                cmd.Parameters.AddWithValue("anExternalID", serializer.GetExternalID(item).ToString());     //  nvarchar(255),
                cmd.Parameters.AddWithValue("aCollectionID", collection.ExternalIDGuid().ToString());                  //  uniqueidentifier,

                // build an xml document containing all index entries (empty is OK)

                XElement listElement = new XElement("IndexEntriesList");

                int externalIDPartCount = 0;
                int partCount = 0;
                foreach (KeyValuePair<string, int> kvp in serializer.GetKeys())
                {
                    if (kvp.Value != 0)
                        continue;
                    XElement entryElement = new XElement("IndexEntry");
                    entryElement.Add(new XElement("Pid", kvp.Key));
                    string value = serializer.GetValueForPath(item, "item." + kvp.Key).ToString();
                    entryElement.Add(new XElement("Val", value));
                    listElement.Add(entryElement);
                    externalIDPartCount++;
                }

                // don't include the externalID if there is only one property contributing to the externalid
                if (externalIDPartCount == 1)
                {
                    listElement = new XElement("IndexEntriesList");
                    externalIDPartCount = 0;
                }

                partCount = externalIDPartCount;

                foreach (KeyValuePair<string, int> kvp in serializer.GetKeys())
                {
                    if (kvp.Value == 0)
                        continue;
                    object value = serializer.GetValueForPath(item, "item." + kvp.Key);
                    if (value != null && !string.IsNullOrEmpty(value.ToString()))
                    {
                        XElement entryElement = new XElement("IndexEntry");
                        entryElement.Add(new XElement("Pid", kvp.Key));
                        entryElement.Add(new XElement("Val", value));
                        listElement.Add(entryElement);
                    }
                    partCount++;
                }

                if (partCount == 0)
                    return true;

                cmd.Parameters.AddWithValue("xml_data", listElement.ToString());

                return ExecuteSaveCmd(cmd, item, serializer);
            }
            catch (Exception ex)
            {
                LoggerWrapper(MajorEnum.Error, MinorEnum.ErrorSavingItems,
                    "Error encountered saving {0}: {1}", new object[] { item.ToString(), ex }, serializer.Logger);
                return false;
            }
        }

        private bool ExecuteSaveCmd(MySqlCommand cmd, object item, ISerializerType serializer)
        {
            LoggerDelegate logger = serializer.Logger;
            Exception originalException = null;
            int retries = 0;
            while (true)
            {
                try
                {
                    cmd.ExecuteNonQuery();
                    LoggerWrapper(MajorEnum.Trace, MinorEnum.ItemSaved, "Item {0} was saved", new object[] { serializer.GetExternalID(item) }, logger);
                    break;
                }
                catch (MySqlException sex)
                {
                    originalException = originalException == null ? sex : originalException;
                    if (++retries > MAXRETRIES)
                    {
                        LoggerWrapper(MajorEnum.Error, MinorEnum.RetryCountExceeded,
                            "retries exceeded saving {0} - original exception - {1} - final exception {2}",
                            new object[] { serializer.GetExternalID(item), originalException, sex }, logger);
                        return false;
                    }
                    Thread.Sleep((int)Math.Pow(RETRYBACKOFFDELTA, retries));
                }
                finally
                {
                    cmd.Dispose();
                }
            }
            return true;
        }

        private bool ExecuteSaveCmd(object item, CollectionType collection, MySqlConnection connection, MySqlTransaction transaction, ISerializerType serializer)
        {
            try
            {
                using (MySqlCommand cmd = connection.CreateCommand())
                {
                    cmd.CommandTimeout = 180;

                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    cmd.CommandText = string.Format("{0}.SaveItems0404", DatabaseName.ToLower());
                    cmd.Parameters.AddWithValue("anExternalID", serializer.GetExternalID(item).ToString());
                    cmd.Parameters.AddWithValue("aCollectionID", collection.ExternalIDGuid().ToString());
                    cmd.Parameters.AddWithValue("theObjectText", serializer.GetDataForObject(item).ToString());
                    cmd.ExecuteNonQuery();
                    LoggerWrapper(MajorEnum.Trace, MinorEnum.ItemSaved, "Item {0} was saved", new object[] { serializer.GetExternalID(item) }, serializer.Logger);
                    return true;
                }
            }
            catch (MySqlException sex)
            {
                if (sex.Message.StartsWith("Duplicate entry"))
                {
                    LoggerWrapper(MajorEnum.Warning, MinorEnum.DuplicateExternalID,
                        "There is already an item with ExternalID {0}", new object[] { serializer.GetExternalID(item) }, serializer.Logger);
                    return false;
                }
                else
                {
                    LoggerWrapper(MajorEnum.Error, MinorEnum.ErrorSavingItems, "SaveItems Error: {0}", new object[] { sex }, serializer.Logger);
                }
            }
            catch (Exception ex)
            {
                if (ex.InnerException == null)
                {
                    LoggerWrapper(MajorEnum.Error, MinorEnum.ErrorSavingItems, "SaveItems Error: {0}", new object[] { ex }, serializer.Logger);
                }
                else
                {
                    LoggerWrapper(MajorEnum.Error, MinorEnum.ErrorSavingItems, "SaveItems Error: {0}", new object[] { ex.InnerException }, serializer.Logger);
                }
            }
            return false;
        }

        class QueryType
        {
            public bool NotViable { get; set; }

            public int ClauseCount { get; set; }

            public string QueryText
            {
                get
                {
                    return NotViable ? " WHERE item.CollectionID = '{0}' " : QueryHeaderText + " WHERE item.CollectionID = '{0}' AND " + QueryClauseText;
                }
            }

            public string QueryHeaderText { get; set; }

            public string QueryClauseText { get; set; }

            private const string QueryClauseTextLiteral =
                @"        i{0}.CollectionID = '{{0}}' AND 
        item.ExternalID = i{0}.ItemExternalID AND
        i{0}.PropertyName = '{1}' AND
        i{0}.PropertyValue = '{2}' 
";

            public QueryType()
            {
                QueryHeaderText = string.Empty;
                QueryClauseText = string.Empty;
                NotViable = true;
            }

            public void AddClause(string propertyName, object propertyValue)
            {
                ClauseCount++;
                QueryHeaderText += string.Format(", ItemIndex i{0} ", ClauseCount);
                QueryClauseText += (ClauseCount > 1 ? " AND " : string.Empty) +
                    string.Format(QueryClauseTextLiteral, ClauseCount, propertyName, propertyValue);
                NotViable = false;
            }

            public void AddExternalIDClause(object value)
            {
                ClauseCount++;
                QueryClauseText += (ClauseCount > 1 ? " AND " : string.Empty) +
                    string.Format(" item.ExternalID = '{0}' ", value);
                NotViable = false;
            }
        }

        /// <summary>
        /// Building things like
        /// 
        /// select * from item i, ItemIndex i1, ItemIndex i2 where
        ///       i.CollectionID = i1.CollectionID AND 
        ///       i.ExternalID = i1.ItemExternalID AND
        ///       i1.PropertyName = 'Origin' AND
        ///       i1.PropertyValue = 'Rhodesia' AND
        ///       i.CollectionID = i2.CollectionID AND 
        ///       i.ExternalID = i2.ItemExternalID AND
        ///       i2.PropertyName = 'Status' AND
        ///       i2.PropertyValue = 'Current' 
        /// 
        /// </summary>
        /// <param name="sqlQuery"></param>
        /// <param name="selectionExpression"></param>
        /// <param name="collection"></param>
        /// <param name="logger"></param>
        /// <returns></returns>
        private QueryType BuildQuery(QueryType sqlQuery, IBooleanValue selectionExpression, CollectionType collection, ISerializerType serializer)
        {
            if (selectionExpression == null)
                return sqlQuery;
            if (selectionExpression is LogicalExpressionType)
                return BuildQuery(sqlQuery, (LogicalExpressionType)selectionExpression, collection, serializer);
            else
                return BuildQuery(sqlQuery, (BooleanExpressionType)selectionExpression, collection, serializer);
        }

        private QueryType BuildQuery(QueryType sqlQuery, LogicalExpressionType selectionExpression, CollectionType collection, ISerializerType serializer)
        {
            if (selectionExpression == null)
                return sqlQuery;
            if (selectionExpression.ExpressionOperator == LogicalOperatorsEnum.AND)
            {
                foreach (int index in new int[] { 0, 1 })
                {
                    if (selectionExpression.Operands[index] is LogicalExpressionType)
                        BuildQuery(sqlQuery, (LogicalExpressionType)selectionExpression.Operands[index], collection, serializer);
                    else if (selectionExpression.Operands[index] is BooleanExpressionType)
                        BuildQuery(sqlQuery, (BooleanExpressionType)selectionExpression.Operands[index], collection, serializer);
                    else if (selectionExpression.Operands[index] is BooleanAtomType)
                        BuildQuery(sqlQuery, (BooleanAtomType)selectionExpression.Operands[index], collection, serializer);
                    else
                        LoggerWrapper(MajorEnum.Error, MinorEnum.InvalidQuery, "Logical expression parameters must be a logical expression, a boolean expression or a boolean atom - scanning: {0}", new object[] { selectionExpression.Operands[index].ToString() }, serializer.Logger);
                }
            }
            else if (selectionExpression.ExpressionOperator == LogicalOperatorsEnum.OR)
            {
                sqlQuery.NotViable = true;
            }
            else if (selectionExpression.ExpressionOperator == LogicalOperatorsEnum.NOT)
            {
                sqlQuery.NotViable = true;
            }
            return sqlQuery;
        }

        private object LiteralString(AtomType atom)
        {
            if (!atom.IsLiteral)
                throw new ArgumentException(string.Format("The atom {0} is of an unexpected type - literal expected", atom.ToString()));
            if (atom is DateTimeAtomType)
                return DateTime.SpecifyKind(((DateTimeAtomType)atom).AtomValue.Value, DateTimeKind.Local);
            if (atom is StringAtomType)
                return ((StringAtomType)atom).AtomValue;
            if (atom is IntegerAtomType)
                return ((IntegerAtomType)atom).AtomValue.Value;
            if (atom is BooleanAtomType)
                return ((BooleanAtomType)atom).AtomValue.Value;
            if (atom is FloatAtomType)
                return ((FloatAtomType)atom).AtomValue.Value;
            else
                return atom.ToString();
        }

        private QueryType BuildQuery(QueryType sqlQuery, RulesEngine.BooleanExpressionType selectionExpression, CollectionType collection, ISerializerType serializer)
        {
            AtomType namedValue =
                selectionExpression.Operands[0].IsNamedValue ? (AtomType)selectionExpression.Operands[0] :
                selectionExpression.Operands[1].IsNamedValue ? (AtomType)selectionExpression.Operands[1] : null;

            AtomType literalValue =
                selectionExpression.Operands[0].IsLiteral ? (AtomType)selectionExpression.Operands[0] :
                selectionExpression.Operands[1].IsLiteral ? (AtomType)selectionExpression.Operands[1] : null;

            if (namedValue == null || literalValue == null)
            {
                LoggerWrapper(MajorEnum.Error, MinorEnum.InvalidQuery, "Invalid boolean expression. Each boolean clause must consist of a literal and a named value: {0}", new object[] { selectionExpression.ToString() }, serializer.Logger);
                return sqlQuery;
            }

            string[] parts = namedValue.AtomName.Split('.');

            string path = parts[parts.Length - 1];

            string externalIDValue = string.Empty;

            KeyValuePair<string, int> kvp = serializer.GetKeys().FirstOrDefault(kv => kv.Key == path);
            // must be either the externalid or the first part of the key
            // this is still only a partial solution as it will ignore subsequent key components
            if (!(path.ToLower() == "externalid" || kvp.Key == path && serializer.GetKeys().First(k => k.Value == kvp.Value).Key == kvp.Key))
                return sqlQuery;

            switch (selectionExpression.ExpressionOperator)
            {
                case CIP4.RulesEngine.BooleanOperatorsEnum.EQL:
                    if (parts.Length == 1 && parts[0].ToLower() == "externalid")
                    {
                        sqlQuery.AddExternalIDClause(LiteralString(literalValue));
                    }
                    else if (kvp.Value == 0)
                    {
                        // ToDo: allow for multi-part externalids - currently the code will only search via
                        // externalID if it has one part
                        if (serializer.GetKeys().Count(kvpe => kvpe.Value == 0) == 1)
                            sqlQuery.AddExternalIDClause(ExternalIDType.ExternalIDEscape(LiteralString(literalValue).ToString()));
                        else
                            sqlQuery.NotViable = true;
                    }
                    else
                    {
                        sqlQuery.AddClause(path, LiteralString(literalValue));
                    }
                    break;
                case CIP4.RulesEngine.BooleanOperatorsEnum.NEQ:
                case CIP4.RulesEngine.BooleanOperatorsEnum.GEQ:
                case CIP4.RulesEngine.BooleanOperatorsEnum.GTR:
                case CIP4.RulesEngine.BooleanOperatorsEnum.LEQ:
                case CIP4.RulesEngine.BooleanOperatorsEnum.LSS:
                case CIP4.RulesEngine.BooleanOperatorsEnum.STARTSWITH:
                case CIP4.RulesEngine.BooleanOperatorsEnum.CONTAINS:
                    sqlQuery.NotViable = true;
                    break;
            }
            return sqlQuery;
        }

        private QueryType BuildQuery(QueryType sqlQuery, RulesEngine.BooleanAtomType selectionExpression, CollectionType collection, ISerializerType serializer)
        {
            sqlQuery.NotViable = true;
            return sqlQuery;
        }

        private List<object> GetItemsFromTheTableViaExternalID(CollectionType collection, string externalID, IBooleanValue selectionExpression, CursorType cursor)
        {
            List<object> result = new List<object>();
            try
            {
                using (MySqlConnection connection = new MySqlConnection(InnerConnectionString))
                {
                    connection.Open();
                    using (MySqlCommand cmd = connection.CreateCommand())
                    {
                        cmd.CommandTimeout = 180;

                        cmd.CommandType = System.Data.CommandType.StoredProcedure;
                        cmd.CommandText = string.Format("`{0}`.`GetItemsByExternalID0404`", DatabaseName.ToLower());
                        cmd.Parameters.AddWithValue("aCollectionID", collection.ExternalIDGuid().ToString());
                        cmd.Parameters.AddWithValue("anExternalID", externalID);

                        result = populateItemsFromDataReader(collection, cmd.ExecuteReader(), cursor, selectionExpression);
                    }
                }
            }

            catch (Exception ex)
            {
                LoggerWrapper(MajorEnum.Error, MinorEnum.ErrorSavingItems, "SaveItems Error: {0}", new object[] { ex }, cursor.Logger);
            }

            return result;
        }

        private List<object> GetItemsFromTheTable(CollectionType collection, IBooleanValue selectionExpression, CursorType cursor)
        {
            List<object> result = new List<object>();
            try
            {
                ISerializerType serializer = cursor.GetSerializer(SerializerKindEnum.CIP4Xml);
                string filterClause = string.Empty; // BuildQuery(collection, selectionExpression, cursor);
                QueryType query = BuildQuery(new QueryType(), selectionExpression, collection, serializer);
                if (!query.NotViable && query.ClauseCount > 0)
                    filterClause = string.Format(query.QueryText, collection.ExternalIDGuid().ToString());
                else
                {
                    if (cursor.FailOnLinearSearch)
                        if (!LoggerWrapper(MajorEnum.Error, MinorEnum.FailOnLinearSearch,
                            "Failed on linear search accessing {0}", new object[] { collection }, cursor.Logger))
                            return new List<object>();

                    filterClause = string.Format(query.QueryText, collection.ExternalIDGuid().ToString());
                }
                string markerClause = string.IsNullOrEmpty(cursor.Marker) ? string.Empty : string.Format("and ID > {0}", cursor.Marker);
                if (cursor.PageSize > 0)
                    markerClause += string.Format(" ORDER BY ID LIMIT 0, {0}", cursor.PageSize);
                using (MySqlConnection connection = new MySqlConnection(InnerConnectionString))
                {
                    connection.Open();
                    using (MySqlCommand cmd = connection.CreateCommand())
                    {
                        cmd.CommandTimeout = 180;

                        cmd.CommandType = System.Data.CommandType.Text;
                        cmd.CommandText = string.Format("select * from item  {0} {1}", filterClause, markerClause);

                        result = populateItemsFromDataReader(collection, cmd.ExecuteReader(), cursor, selectionExpression);
                    }
                }
            }

            catch (Exception ex)
            {
                if (ex.Message.StartsWith("Unable to keep going on Error - Failed on linear search accessing"))
                    throw;
                LoggerWrapper(MajorEnum.Error, MinorEnum.GetItems, "GetItems Error: {0}", new object[] { ex }, cursor.Logger);
            }

            return result;
        }

        private List<object> populateItemsFromDataReader(CollectionType collection, MySqlDataReader dr, CursorType cursor, IBooleanValue selectionExpression)
        {
            List<object> result = new List<object>();

            while (dr.Read())
            {
                ISerializerType serializer = cursor.GetSerializer(SerializerKindEnum.CIP4Xml);

                string xmlText = dr.GetString(COLUMN_OBJECT_TEXT);

                XElement element = XElement.Parse(xmlText);

                object item = serializer.GetObjectForData(element);

                if (selectionExpression != null)
                    if (!CompareProperties(selectionExpression, item, cursor.GetSerializer(SerializerKindEnum.CIP4Xml)))
                        continue;

                cursor.Marker = getInt64(dr, COLUMN_ID_ID).ToString();

                result.Add(item);

                // must call serializer before streamResolver as the serializer will pick up the 
                // objects that own the properties that the object's strems have to be bound to
                GetItemsStreamResolver(serializer, collection, serializer.GetExternalID(item).ToString());

            }

            return result;
        }

        #region Column Names

        private const string COLUMN_ITEM_ID = "ItemID";
        private const string COLUMN_STATUS_ID = "StatusID";
        private const string COLUMN_ITEM_STATUS_ID = "ItemStatusID";
        private const string COLUMN_TRANSACTION_ID = "TransactionID";
        private const string COLUMN_CREATED_TRANSACTION_ID = "CreatedTransactionID";
        private const string COLUMN_DELETED_TRANSACTION_ID = "DeletedTransactionID";
        private const string COLUMN_LAST_MOD_TRANSACTION_ID = "LastModTransactionID";
        private const string COLUMN_OBJECT_TEXT = "ObjectText";
        private const string COLUMN_HASH_VALUE = "HashValue";
        private const string COLUMN_CHANGE_HISTORY_TEXT = "ChangeHistoryText";
        private const string COLUMN_STATUS_MESSAGE = "StatusMessage";
        private const string COLUMN_STATE_MESSAGE = "StateMessage";
        private const string COLUMN_ID = "ID";
        private const string COLUMN_EXTERNAL_ID = "ExternalID";
        private const string COLUMN_ITEMTYPEVERSION = "ItemtypeVersion";
        private const string COLUMN_ENTERED_DATE = "EnteredDate";
        private const string COLUMN_LAST_MOD_DATE = "LastModDate";
        private const string COLUMN_COLLECTION_ID = "CollectionID";
        private const string COLUMN_ITEM_MESSAGE_SEVERITY_TYPE_ID = "ItemMessageSeverityTypeID";
        private const string COLUMN_ID_ID = "ID";

        #endregion

        private object populateItemFromDataReader(MySqlDataReader dr, CursorType cursor, IBooleanValue selectionExpression)
        {
            if (!dr.Read())
            {
                return null;
            }

            string xmlText = dr.GetString(COLUMN_OBJECT_TEXT);

            XElement element = XElement.Parse(xmlText);

            object result = cursor.GetSerializer(SerializerKindEnum.CIP4Xml).GetObjectForData(element);

            if (selectionExpression != null)
                if (!CompareProperties(selectionExpression, result, cursor.GetSerializer(SerializerKindEnum.CIP4Xml)))
                    return null;

            cursor.Marker = getInt64(dr, COLUMN_ID_ID).ToString();

            return result;
        }

        private static Int64 getInt64(MySqlDataReader dr, string column)
        {
            Int64 result = -1;
            bool isParsed = false;

            //check to see if the column is null before trying to parse
            if (dr[column] != DBNull.Value)
            {
                isParsed = Int64.TryParse(dr[column].ToString(), out result);
            }

            if (!isParsed)
            {
                result = -1;
            }

            return result;
        }

        private static Int32 getInt32(MySqlDataReader dr, string column)
        {
            Int32 result = -1;

            //check to see if the column is null before trying to parse
            if (dr[column] != DBNull.Value)
            {
                result = Convert.ToInt32(dr[column].ToString());
            }

            return result;
        }

        private static DateTime getDate(MySqlDataReader dr, string column)
        {
            DateTime result = DateTime.MinValue;
            bool isParsed = false;

            if (dr[column] != DBNull.Value)
            {
                isParsed = DateTime.TryParse(dr[column].ToString(), out result);
            }

            if (!isParsed)
            {
                result = DateTime.MinValue;
            }

            return result;
        }

        private static String getString(MySqlDataReader dr, string column)
        {
            string result = string.Empty;

            if (dr[column] != DBNull.Value)
            {
                result = dr[column].ToString();
            }

            return result;
        }

        private static Int64 getOptionalInt64(MySqlDataReader dr, string column)
        {
            Int64 result = -1;
            bool isParsed = false;

            //check to see if the column is null before trying to parse
            if (dr[column] != DBNull.Value)
            {
                isParsed = Int64.TryParse(dr[column].ToString(), out result);
            }

            if (!isParsed)
            {
                result = -1;
            }

            return result;
        }

        private static Guid? getOptionalGuid(MySqlDataReader dr, string column)
        {
            Guid? result = null;

            if (dr[column] != DBNull.Value)
            {
                result = new Guid(dr[column].ToString());
            }
            return result;
        }

        private static Int32 getOptionalInt32(MySqlDataReader dr, string column)
        {
            Int32 result = -1;
            bool isParsed = false;

            //check to see if the column is null before trying to parse
            if (dr[column] != DBNull.Value)
            {
                isParsed = Int32.TryParse(dr[column].ToString(), out result);
            }

            if (!isParsed)
            {
                result = -1;
            }

            return result;
        }

        private static DateTime getOptionalDate(MySqlDataReader dr, string column)
        {
            DateTime result = DateTime.MinValue;
            bool isParsed = false;

            if (dr[column] != DBNull.Value)
            {
                isParsed = DateTime.TryParse(dr[column].ToString(), out result);
            }

            if (!isParsed)
            {
                result = DateTime.MinValue;
            }

            return result;
        }

        private static String getOptionalString(MySqlDataReader dr, string column)
        {
            try
            {
                return dr[column].ToString();
            }
            catch
            {
                return string.Empty;
            }
        }

        /// <summary>
        /// Compresses a string using GZip.  Note:  This is countereffective for strings of less than 3-400 characters or so.
        /// </summary>
        /// <param name="text">Text to compress</param>
        /// <returns>compressed string</returns>
        public static string Compress(string text)
        {
            if (string.IsNullOrEmpty(text))
            {
                return string.Empty;
            }

            byte[] buffer = Encoding.UTF8.GetBytes(text);
            MemoryStream ms = new MemoryStream();
            using (GZipStream zip = new GZipStream(ms, CompressionMode.Compress, true))
            {
                zip.Write(buffer, 0, buffer.Length);
            }
            ms.Position = 0;
            MemoryStream outStream = new MemoryStream();
            byte[] compressed = new byte[ms.Length];
            ms.Read(compressed, 0, compressed.Length);
            byte[] gzBuffer = new byte[compressed.Length + 4];
            System.Buffer.BlockCopy(compressed, 0, gzBuffer, 4, compressed.Length);
            System.Buffer.BlockCopy(BitConverter.GetBytes(buffer.Length), 0, gzBuffer, 0, 4);
            return Convert.ToBase64String(gzBuffer);
        }

        /// <summary>
        /// Decompresses a GZipped-deflated string.
        /// </summary>
        /// <param name="compressedText">compressed text</param>
        /// <returns>decompressed string</returns>
        public static string Decompress(string compressedText)
        {
            if (string.IsNullOrEmpty(compressedText))
            {
                return string.Empty;
            }

            byte[] gzBuffer = Convert.FromBase64String(compressedText);
            using (MemoryStream ms = new MemoryStream())
            {
                int msgLength = BitConverter.ToInt32(gzBuffer, 0);
                ms.Write(gzBuffer, 4, gzBuffer.Length - 4);
                byte[] buffer = new byte[msgLength];
                ms.Position = 0;
                using (GZipStream zip = new GZipStream(ms, CompressionMode.Decompress))
                {
                    zip.Read(buffer, 0, buffer.Length);
                }
                return Encoding.UTF8.GetString(buffer);
            }
        }

        private string getHashValue(string text)
        {
            System.Security.Cryptography.MD5CryptoServiceProvider md5 = new System.Security.Cryptography.MD5CryptoServiceProvider();
            char[] charValue = text.ToCharArray();
            byte[] byteValue = new byte[text.Length * 2];
            string result = string.Empty;

            for (Int32 index = 0; index < charValue.Length; index++)
            {
                byteValue[index * 2] = System.Convert.ToByte((charValue[index] & 0xFF00) >> 8);
                byteValue[index * 2 + 1] = System.Convert.ToByte(charValue[index] & 0xFF);
            }

            result = BitConverter.ToString(md5.ComputeHash(byteValue));
            return result;
        }

        private MySqlConnection getAndOpenConnection(LoggerDelegate logger)
        {
            MySqlConnection connection = new MySqlConnection(InnerConnectionString);
            int retryCount = 0;
            while (retryCount < 5)
                if (!PingHost(connection.DataSource, logger))
                    retryCount++;
                else
                    break;
            if (retryCount == 5)
                LoggerWrapper(MajorEnum.Error, MinorEnum.DataSourceNotReachable,
                    "The DataSource specified in the conection is not reachable: DataSource={0}", new object[] { connection.DataSource }, logger);
            else
                connection.Open();
            return connection;
        }

        private bool PingHost(string host, LoggerDelegate logger)
        {
            string actualHostName = host.TrimStart(new char[] { '\\' }).Split(new char[] { '\\' })[0];
            if (string.Compare(actualHostName, "localhost", true) == 0)
                return true;
            if (string.Compare(actualHostName, Environment.MachineName, true) == 0)
                return true;
            System.Net.NetworkInformation.Ping pinger = new System.Net.NetworkInformation.Ping();
            System.Net.NetworkInformation.PingOptions pingerOptions = new System.Net.NetworkInformation.PingOptions();
            string data = "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa";
            byte[] buffer = Encoding.ASCII.GetBytes(data);
            int timeout = 2000;
            System.Net.NetworkInformation.PingReply reply = pinger.Send(actualHostName, timeout, buffer, pingerOptions);
            if (reply.Status == System.Net.NetworkInformation.IPStatus.Success)
                return true;
            else
            {
                LoggerWrapper(MajorEnum.Error, MinorEnum.DataSourceNotReachable,
                    "Unable to reach database host {0} - reply status {1}", new object[] { host, reply.Status }, logger);
                return false;
            }
        }

        private void GetItemsStreamResolver(ISerializerType serializer, CollectionType collection, string externalID)
        {
            string collectionFolder = collection.ExternalIDGuid().ToString();
            foreach (KeyValuePair<string, KeyValuePair<PropertyInfo, object>> streamSeed in serializer.StreamSeeds)
            {
                if (streamSeed.Value.Value == null)
                    continue;
                Stream str = new CIP4FileStream(collection.ExternalIDGuid().ToString(), externalID, streamSeed.Key, getAndOpenConnection, serializer.Logger);
                streamSeed.Value.Key.SetValue(streamSeed.Value.Value, str, new object[] { });
            }

        }

        private void SaveItemsStreamResolver(ISerializerType serializer, CollectionType collection, MySqlConnection connection, MySqlTransaction transaction, string externalID)
        {
            foreach (KeyValuePair<string, KeyValuePair<PropertyInfo, object>> streamSeed in serializer.StreamSeeds)
            {
                if (streamSeed.Value.Value == null)
                    continue;
                Stream source = (Stream)streamSeed.Value.Value;
                if (source != null)
                {
                    {
                        {
                            using (MySqlCommand command = connection.CreateCommand())
                            {
                                command.CommandText =
                                    "INSERT INTO BinaryData " +
                                    "(Id, CollectionID, ExternalID, Path, Data) " +
                                    "VALUES (?Id, ?CollectionID, ?ExternalID, ?Path, ?Data)";
                                command.CommandType = System.Data.CommandType.Text;

                                Guid idGuid = Guid.NewGuid();

                                command.Parameters.AddWithValue("Id", idGuid.ToString());
                                command.Parameters.AddWithValue("CollectionID", collection.ExternalIDGuid().ToString());
                                command.Parameters.AddWithValue("ExternalID", externalID);
                                command.Parameters.AddWithValue("Path", streamSeed.Key);

                                byte[] buffer = new byte[source.Length];

                                source.Read(buffer, 0, (int)source.Length);

                                command.Parameters.AddWithValue("Data", buffer);

                                int rowinserted = command.ExecuteNonQuery();

                                if (rowinserted != 1)
                                {
                                    LoggerWrapper(MajorEnum.Error, MinorEnum.ErrorSavingItems, "Unable to save the stream property {0} for item {1}", new object[] { streamSeed.Key, externalID }, serializer.Logger);
                                }

                            }
                        }
                    }
                    source.Close();
                    source.Dispose();
                }
            }

        }

        private void RemoveItemsStreamResolver(ISerializerType serializer, CollectionType collection, string externalID)
        {
            foreach (KeyValuePair<string, KeyValuePair<PropertyInfo, object>> streamSeed in serializer.StreamSeeds)
            {
                int retryCount = 0;
                do
                {
                    try
                    {
                        using (MySqlConnection connection = new MySqlConnection(InnerConnectionString))
                        {
                            connection.Open();

                            MySqlCommand cmd = connection.CreateCommand();
                            cmd.CommandTimeout = 180;

                            cmd.CommandType = System.Data.CommandType.Text;
                            cmd.CommandText =
                                string.Format("delete from BinaryData where CollectionID = '{0}' AND ExternalID = '{1}' AND Path = '{2}'",
                                collection.ExternalIDGuid().ToString(), externalID, streamSeed.Key);

                            cmd.ExecuteNonQuery();

                            break;
                        }
                    }
                    catch (Exception ex)
                    {
                        retryCount += 1;

                        if (ex.Message.StartsWith("The DataSource specified in the conection is not reachable"))
                            LoggerWrapper(MajorEnum.Warning, MinorEnum.RetryAccessingDataStore,
                                "Retrying GetItemsFromTheTable because of {0}", new object[] { ex.Message }, serializer.Logger);
                        else if (ex.Message.EndsWith("TimedOut"))
                            LoggerWrapper(MajorEnum.Warning, MinorEnum.RetryAccessingDataStore,
                                "Retrying GetItemsFromTheTable because of {0}", new object[] { ex.Message }, serializer.Logger);
                        else
                            LoggerWrapper(MajorEnum.Error, MinorEnum.ErrorGettingItemsFromProvider, "Unexpected error removing a stream: {0}", new object[] { ex }, serializer.Logger);

                        Thread.Sleep((int)Math.Pow(RETRYBACKOFFDELTA, retryCount));
                    }
                } while (retryCount < MAXRETRIES);
                if (retryCount == MAXRETRIES)
                {
                    LoggerWrapper(MajorEnum.Error, MinorEnum.RetryCountExceeded, "Retry count exceeded for collection {0}", new object[] { collection.Name }, serializer.Logger);
                }
            }

        }

        #endregion

        #region Query Building

        // ToDo - figure out what this should look like for MySql

        // Queries are built from template items and can include multiple properties, the properties can be sv or mv, simple or complex.
        // Ultimately query clauses are comparisons of simple values - currently clauses can only be 'anded' together (no 'ors'). For
        // example, consider  the following template
        //
        //<cic:publish xmlns:cic="http://cipl.codeplex.com/CICl1.xsd" xmlns:cir="http://cipl.codeplex.com/CIPlRule1.xsd" xmlns:cii="http://cipl.codeplex.com/CIPlItem1.xsd" xmlns:cio="http://cipl.codeplex.com/CIPlOther1.xsd">
        //  <cic:foreach cic:destination="cxdx.net/directory?CompoundTypes">
        //    <cii:Item>
        //      <cio:CompoundType>
        //        <cio:TypeProperties cio:Count="1">
        //          <cio:TypePropertyType>
        //            <cio:Type>
        //              <cio:TypeBaseType.Path>CIP4://cxdx.net/directory?compoundtypes[cxdx.net.directory.organizationtype]</cio:TypeBaseType.Path>
        //            </cio:Type>
        //            <cio:Reference>
        //              <cio:BooleanType>true</cio:BooleanType>
        //            </cio:Reference>
        //          </cio:TypePropertyType>
        //        </cio:TypeProperties>
        //      </cio:CompoundType>
        //    </cii:Item>
        //    <cic:do>
        //      <cir:StringAtom AtomSource="namedValue" Name="current.ExternalID" />
        //    </cic:do>
        //  </cic:foreach>
        //</cic:publish>
        //                 
        // 
        // Basically this means "get the ExternalID for any CompoundType that has a reference property that ranges over the organizationtype".
        //         
        // The Sql produced by BuildItemQuery looks like everything in what follows after the first two lines (i.e. from the first 'AND' onwards):
        // 
        // 
        //select * from Item
        //where CollectionID = '{5dff9aff-3fa4-4df4-b588-ea22edd49d59}'
        // AND 'CIP4://cxdx.net/directory?compoundtypes[cxdx.net.directory.organizationtype]' = SOME (
        //  select LOWER(T2.Loc.query('.').value('.', 'nvarchar(max)'))
        //  from Item I2
        //  CROSS APPLY I2.ObjectText.nodes('
        //  declare namespace cii="http://cipl.codeplex.com/CIPlItem1.xsd";
        //  declare namespace cio="http://cipl.codeplex.com/CIPlOther1.xsd";
        //  cii:Item/cio:CompoundType/cio:TypeProperties/*/cio:Type/.') AS T2(Loc) 
        //  WHERE Item.PublishedID = I2.PublishedID) AND 'true' = SOME (
        //  select T2.Loc.query('.').value('.', 'nvarchar(max)')
        //  from Item I2
        //  CROSS APPLY I2.ObjectText.nodes('
        //  declare namespace cii="http://cipl.codeplex.com/CIPlItem1.xsd";
        //  declare namespace cio="http://cipl.codeplex.com/CIPlOther1.xsd";
        //  cii:Item/cio:CompoundType/cio:TypeProperties/*/cio:Reference/.') AS T2(Loc) 
        //  WHERE Item.PublishedID = I2.PublishedID) 
        //
        // Because the query involves two multi-valued properties, the Sql produced has two SOME clauses
        // added together - the query is probably not quite what the user intended as it really says
        // "get me any CompoundType where any property has OrganizationType as its type and any (possibly other)
        // property is a reference". It'll do for now...

        static List<string> QueryForms = new List<string>{
            // 0=path, 1=operator, 2=value
            // SVForm =
                " AND ObjectText.value(" +
                "   'declare namespace cii=\"" + Globals.ciiNS + "\";" + Environment.NewLine +
                "   declare namespace cio=\"" + Globals.cioNS + "\";"  + Environment.NewLine + 
                "   ({0})[1]', 'nvarchar(max)') {1} {2}",     

            // MVForm = 
                 " AND {2} {1} SOME (" + Environment.NewLine +
                 "  select T2.Loc.query('.').value('.', 'nvarchar(max)')" + Environment.NewLine +
                 "  from Item I2" + Environment.NewLine +
                 "  CROSS APPLY I2.ObjectText.nodes('" + Environment.NewLine +
                 "  declare namespace cii=\"" + Globals.ciiNS + "\";" + Environment.NewLine +
                 "  declare namespace cio=\"" + Globals.cioNS + "\";" + Environment.NewLine +
                 "  {0}') AS T2(Loc) " + Environment.NewLine +
                 "  WHERE Item.PublishedID = I2.PublishedID)",                                                        

            //MVReferenceForm =
                 " AND {2} {1} SOME (" + Environment.NewLine +
                 "  select LOWER(T2.Loc.query('.').value('.', 'nvarchar(max)'))" + Environment.NewLine +
                 "  from Item I2" + Environment.NewLine +
                 "  CROSS APPLY I2.ObjectText.nodes('" + Environment.NewLine +
                 "  declare namespace cii=\"" + Globals.ciiNS + "\";" + Environment.NewLine +
                 "  declare namespace cio=\"" + Globals.cioNS + "\";" + Environment.NewLine +
                 "  {0}') AS T2(Loc) " + Environment.NewLine +
                 "  WHERE Item.PublishedID = I2.PublishedID)"
        };

        enum QueryFormsEnum
        {
            SVForm,
            MVForm,
            MVReferenceForm
        }

        //private string BuildItemQuery(ItemXmlType item, CollectionType collection)
        //{
        //    // Sql Server xpath does not support contains or starts-with - so  
        //    // have to do something like
        //    //
        //    //      select * from item 
        //    //      where 
        //    //          ObjectText.value(
        //    //          'declare namespace cii="http://cipl.codeplex.com/CIPlItem1.xsd";
        //    //          declare namespace cio="http://cipl.codeplex.com/CIPlOther1.xsd";
        //    //          (//cii:Item/cio:FolderType/cio:Name)[1]', 'nvarchar(max)') like '%oo%'
        //    //      and
        //    //          ObjectText.value(
        //    //          'declare namespace cii="http://cipl.codeplex.com/CIPlItem1.xsd";
        //    //          declare namespace cio="http://cipl.codeplex.com/CIPlOther1.xsd";
        //    //          (//cii:Item/cio:FolderType/cio:ParentFolder)[1]', 'nvarchar(max)') like '%CIP4%'
        //    //

        //    string query = "";
        //    if (item.PublishedID.HasValue)
        //        query += string.Format("AND LOWER(PublishedID) = '{0}'", item.PublishedID.ToString().ToLower());

        //    if (!string.IsNullOrEmpty(item.ExternalID))
        //        query += string.Format("AND LOWER(ExternalID) = '{0}'", item.ExternalID.ToLower());

        //    if (item.ItemStatus.HasValue)
        //        query += string.Format("AND StatusID='{0}'", Convert.ToInt32(item.ItemStatus).ToString());

        //    foreach (PropertyType property in item.Properties)
        //    {
        //        query += BuildPropertyQuery(false, property, "cii:Item/cio:" + collection.CollectionCompoundType.Name);
        //    }
        //    return query;
        //}

        //private string BuildCompoundValueQuery(bool multiValued, CompoundValueType value, string path)
        //{
        //    string query = string.Empty;
        //    foreach (PropertyType property in value.Properties)
        //    {
        //        query += BuildPropertyQuery(multiValued, property, path);
        //    }
        //    return query;
        //}

        //private string BuildClause(bool multiValued, string path, PropertyType property, CIP4PrimitiveType primitiveType, string value, ItemTemplateQueryOperatorsEnum op)
        //{
        //    switch (primitiveType)
        //    {
        //        case CIP4PrimitiveType.CIP4Reference:
        //            if (op > ItemTemplateQueryOperatorsEnum.NEQ)
        //                throw new ArgumentException(string.Format("You canot use operator {0} with a reference property {1}", op, property.Name));
        //            if (op == ItemTemplateQueryOperatorsEnum.EQL)
        //                if (multiValued)
        //                    return string.Format(QueryForms[(int)QueryFormsEnum.MVReferenceForm], path, "=",
        //                        SqlValueFormattedForOperator(ItemTemplateQueryOperatorsEnum.EQL, CIP4PrimitiveType.CIP4Reference, value));
        //                else
        //                    return string.Format(QueryForms[(int)QueryFormsEnum.SVForm], path, "LIKE",
        //                        SqlValueFormattedForOperator(ItemTemplateQueryOperatorsEnum.CONTAINS, CIP4PrimitiveType.CIP4Reference, value));
        //            else
        //                if (multiValued)
        //                    return string.Format(QueryForms[(int)QueryFormsEnum.MVReferenceForm], path, "<>",
        //                        SqlValueFormattedForOperator(ItemTemplateQueryOperatorsEnum.NEQ, CIP4PrimitiveType.CIP4Reference, value));
        //                else
        //                    return string.Format(QueryForms[(int)QueryFormsEnum.SVForm], path, "NOT LIKE",
        //                        SqlValueFormattedForOperator(ItemTemplateQueryOperatorsEnum.CONTAINS, CIP4PrimitiveType.CIP4Reference, value));
        //        case CIP4PrimitiveType.CIP4Boolean:
        //            if (op > ItemTemplateQueryOperatorsEnum.NEQ)
        //                throw new ArgumentException(string.Format("You canot use operator {0} with a boolean property {1}", op, property.Name));
        //            if (multiValued)
        //                return string.Format(QueryForms[(int)QueryFormsEnum.MVForm], path, SqlOperatorForm(op),
        //                    SqlValueFormattedForOperator(op, primitiveType, value));
        //            else
        //                return string.Format(QueryForms[(int)QueryFormsEnum.SVForm], path, SqlOperatorForm(op),
        //                    SqlValueFormattedForOperator(op, primitiveType, value));
        //        case CIP4PrimitiveType.CIP4Decimal:
        //        case CIP4PrimitiveType.CIP4Float:
        //        case CIP4PrimitiveType.CIP4Integer:
        //            if (op > ItemTemplateQueryOperatorsEnum.LEQ)
        //                throw new ArgumentException(string.Format("You canot use operator {0} with a numeric property {1}", op, property.Name));
        //            if (multiValued)
        //                return string.Format(QueryForms[(int)QueryFormsEnum.MVForm], path, SqlOperatorForm(op),
        //                    SqlValueFormattedForOperator(op, primitiveType, value));
        //            else
        //                return string.Format(QueryForms[(int)QueryFormsEnum.SVForm], path, SqlOperatorForm(op),
        //                    SqlValueFormattedForOperator(op, primitiveType, value));
        //        default:
        //            if (multiValued && op > ItemTemplateQueryOperatorsEnum.LEQ)
        //                throw new ArgumentException(string.Format("You canot use operator {0} with a multivalued property {1}", op, property.Name));
        //            if (multiValued)
        //                return string.Format(QueryForms[(int)QueryFormsEnum.MVForm], path, SqlOperatorForm(op),
        //                    SqlValueFormattedForOperator(op, primitiveType, value));
        //            else
        //                return string.Format(QueryForms[(int)QueryFormsEnum.SVForm], path, SqlOperatorForm(op),
        //                    SqlValueFormattedForOperator(op, primitiveType, value));

        //    }
        //}

        //private string BuildPropertyQuery(bool multiValued, PropertyType property, string path)
        //{
        //    if (property is SingleValuedPropertyType)
        //    {
        //        BaseValueType value = ((SingleValuedPropertyType)property).Value;
        //        if (value is CompoundValueType)
        //            return BuildCompoundValueQuery(multiValued, (CompoundValueType)((SingleValuedPropertyType)property).Value, path + "/cio:" + property.Name + "/*");  // use anaonymous reference for type name in case the type is a member of an ineritance graph
        //        else
        //        {
        //            ItemTemplateQueryOperatorsEnum op = ItemTemplateQueryOperatorsEnum.EQL;
        //            CIP4PrimitiveType primitiveType =
        //                value.ValueTypeName == "String" ? CIP4PrimitiveType.CIP4String :
        //                value.ValueTypeName == "DateTime" ? CIP4PrimitiveType.CIP4DateTime :
        //                CIP4PrimitiveType.CIP4Unknown;
        //            return BuildClause(multiValued, path + "/cio:" + property.Name + "/.", property, primitiveType, ((SingleValuedPropertyType)property).Value.ToString(), op);
        //        }
        //    }
        //    else // if (property is MultiValuedPropertyType)
        //    {
        //        string result = string.Empty;
        //        foreach (BaseValueType value in ((MultiValuedPropertyType)property).Values)
        //        {
        //            if (value is CompoundValueType)
        //                result += BuildCompoundValueQuery(true, (CompoundValueType)value, path + "/cio:" + property.Name + "/*");  // use anaonymous reference for type name in case the type is a member of an ineritance graph
        //            else
        //            {
        //                CIP4PrimitiveType primitiveType = value.ValueTypeName == "String" ? CIP4PrimitiveType.CIP4String : CIP4PrimitiveType.CIP4Unknown;
        //                ItemTemplateQueryOperatorsEnum op = ItemTemplateQueryOperatorsEnum.EQL;
        //                result += BuildClause(true, path + "/cio:" + property.Name + "/*.", property, primitiveType, value.ToString(), op);
        //            }
        //        }
        //        return result;
        //    }
        //}

        /// <summary>
        /// Sql LIKE seems to be broken - but anyway - we do our best - see 
        /// 
        ///     http://msdn.microsoft.com/en-us/library/aa933232(SQL.80).aspx
        /// 
        /// For a discussion of escaping for Sql LIKE - reserved characters are []%~_
        /// These can be escaped by declaring an escape literal after the like clause using 
        /// 
        ///     ESCAPE '!'
        ///     
        /// Should be able to escape the escape by putting it in [] - but that doesn't seem to work
        /// As a poor man's alternative, could replace ! with % - but could geta  much broader query
        /// result as a consequence - on balance seems easiest to penalize people for putting ! in 
        /// their data. Put it in as [!] and report it as a Sql bug!
        /// </summary>
        /// <param name="s"></param>
        /// <returns></returns>
        //private string EscapeSqlLikeLiteral(string s)
        //{
        //    return s.Replace("!", "[!]").Replace("[", "![").Replace("]", "!]").Replace("_", "!_").Replace("%", "!%").Replace("^", "!^");
        //}

        //public enum ItemTemplateQueryOperatorsEnum
        //{
        //    EQL,
        //    NEQ,
        //    GTR,
        //    GEQ,
        //    LSS,
        //    LEQ,
        //    STARTSWITH,
        //    CONTAINS
        //}

        //private string SqlOperatorForm(ItemTemplateQueryOperatorsEnum op)
        //{
        //    switch (op)
        //    {
        //        case ItemTemplateQueryOperatorsEnum.EQL:
        //            return "=";
        //        case ItemTemplateQueryOperatorsEnum.NEQ:
        //            return "<>";
        //        case ItemTemplateQueryOperatorsEnum.GTR:
        //            return ">";
        //        case ItemTemplateQueryOperatorsEnum.GEQ:
        //            return ">=";
        //        case ItemTemplateQueryOperatorsEnum.LSS:
        //            return "<";
        //        case ItemTemplateQueryOperatorsEnum.LEQ:
        //            return "<=";
        //        case ItemTemplateQueryOperatorsEnum.STARTSWITH:
        //            return "LIKE";
        //        case ItemTemplateQueryOperatorsEnum.CONTAINS:
        //            return "LIKE";
        //        default:
        //            throw new ApplicationException("Did someone add a new operator?");
        //    }
        //}

        //private string SqlValueFormattedForOperator(ItemTemplateQueryOperatorsEnum op, CIP4PrimitiveType primitiveType, string value)
        //{
        //    switch (op)
        //    {
        //        case ItemTemplateQueryOperatorsEnum.EQL:
        //        case ItemTemplateQueryOperatorsEnum.NEQ:
        //            if (primitiveType == CIP4PrimitiveType.CIP4Boolean)
        //                return "'" + System.Convert.ToBoolean(value).ToString().ToLower() + "'";
        //            if (primitiveType == CIP4PrimitiveType.CIP4String ||
        //                primitiveType == CIP4PrimitiveType.CIP4Guid ||
        //                primitiveType == CIP4PrimitiveType.CIP4DateTime)
        //                return "'" + value + "'";
        //            if (primitiveType == CIP4PrimitiveType.CIP4Reference)
        //                return "'" + value.ToLower() + "'";
        //            return value;
        //        case ItemTemplateQueryOperatorsEnum.GTR:
        //        case ItemTemplateQueryOperatorsEnum.GEQ:
        //        case ItemTemplateQueryOperatorsEnum.LSS:
        //        case ItemTemplateQueryOperatorsEnum.LEQ:
        //            if (primitiveType == CIP4PrimitiveType.CIP4Boolean ||
        //                primitiveType == CIP4PrimitiveType.CIP4Reference)
        //                throw new ArgumentException(string.Format("You cannot use operator {0} with value {1}", op, value));
        //            if (primitiveType == CIP4PrimitiveType.CIP4String ||
        //                primitiveType == CIP4PrimitiveType.CIP4Guid ||
        //                primitiveType == CIP4PrimitiveType.CIP4DateTime)
        //                return "'" + value + "'";
        //            return value;
        //        case ItemTemplateQueryOperatorsEnum.STARTSWITH:
        //            if (!(primitiveType == CIP4PrimitiveType.CIP4String ||
        //                primitiveType == CIP4PrimitiveType.CIP4DateTime))
        //                throw new ArgumentException(string.Format("You cannot use operator {0} with value {1}", op, value));
        //            return EscapeSqlLikeLiteral(value.Substring(0, value.Length - 1)) + "%' ESCAPE '!' ";
        //        case ItemTemplateQueryOperatorsEnum.CONTAINS:
        //            if (!(primitiveType == CIP4PrimitiveType.CIP4String ||
        //                primitiveType == CIP4PrimitiveType.CIP4Reference ||
        //                primitiveType == CIP4PrimitiveType.CIP4DateTime))
        //                throw new ArgumentException(string.Format("You cannot use operator {0} with value {1}", op, value));
        //            if (primitiveType == CIP4PrimitiveType.CIP4Reference)
        //                return "'" + EscapeSqlLikeLiteral(value) + "' ESCAPE '!' ";
        //            else
        //                return "'%" + EscapeSqlLikeLiteral(value) + "%' ESCAPE '!' ";
        //        default:
        //            throw new ApplicationException("Did someone add a new operator?");
        //    }
        //}

        #endregion


    }
}
